package com.irdstudio.apicenter.console.dm;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import org.springframework.stereotype.Component;

import com.irdstudio.apicenter.frame.core.jdbc.util.ConnectionManager;

@Component(value = "appMenuResourceDao")
public class AppMenuResourceDao {

	public List<AppMenuResource> queryMenusByRoleId(String app_role_id,
			String app_id) throws SQLException {
		AppMenuResource dc = null;
		List<AppMenuResource> list = new ArrayList<AppMenuResource>();
		Connection conn = ConnectionManager.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn
					.prepareStatement("	select  a.menu_id,a.menu_name ,a.app_id, "
							+ "CASE  "
							+ "when b.app_role_id!='' then  1  "
							+ "else  0   "
							+ "end  as  checked   "
							+ "from app_menu_resource a left  join  (select *  from app_role_right  where app_role_id='"
							+ app_role_id
							+ "'  and app_id=?  ) as b on  a.menu_id=b.menu_id  where a.app_id=?");
			// ps.setString(1,app_role_id);
			// ps.setString(2,app_id);
			ps.setString(1, app_id);
			ps.setString(2, app_id);
			rs = ps.executeQuery();
			while (rs.next()) {
				dc = new AppMenuResource();
				dc.setMenuId(rs.getString("menu_id"));
				dc.setMenuName(rs.getString("menu_name"));
				dc.setMenuIcon(rs.getString("checked"));
				list.add(dc);
			}
		} catch (SQLException e) {
			throw new SQLException("queryMenusByRoleId is Wrong!"
					+ e.getMessage());
		} finally {
			close2(rs, conn, ps);
		}
		return list;
	}

	/**
	 * 根据主键查询单条记录
	 * 
	 * @param
	 * @return
	 * @throws SQLException
	 */
	public AppMenuResource queryWithKeys(String appId, String menuId)
			throws SQLException {
		AppMenuResource dc = null;
		Connection conn = ConnectionManager.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn
					.prepareStatement("SELECT * FROM app_menu_resource WHERE app_id=? and MENU_ID=?");
			ps.setString(1, appId);
			ps.setString(2, menuId);
			rs = ps.executeQuery();
			if (rs.next()) {
				dc = new AppMenuResource();
				dc.setAppId(rs.getString("app_id"));
				dc.setMenuId(rs.getString("menu_id"));
				dc.setMenuName(rs.getString("MENU_NAME"));
				dc.setParentMenuId(rs.getString("PARENT_MENU_ID"));
				dc.setMenuUrl(rs.getString("MENU_URL"));
				dc.setMenuIcon(rs.getString("MENU_ICON"));
				dc.setMenuSortNo(rs.getString("MENU_SORT_NO"));

			}
		} catch (SQLException e) {
			throw new SQLException("queryAppTradePrivateWithKeys is Wrong!"
					+ e.getMessage());
		} finally {
			close2(rs, conn, ps);
		}
		return dc;
	}

	public int saveAppMenuResource(AppMenuResource appMenuResource)
			throws SQLException {

		Connection conn = ConnectionManager.getConnection();
		PreparedStatement ps = null;
		int flag;
		try {
			String sql = "insert into app_menu_resource values(?,?,?,?,?,?,?);";
			ps = conn.prepareStatement(sql);
			ps.setString(1, appMenuResource.getAppId());
			ps.setString(2, appMenuResource.getMenuId());
			ps.setString(3, appMenuResource.getMenuName());
			ps.setString(4, appMenuResource.getParentMenuId());
			ps.setString(5, appMenuResource.getMenuUrl());
			ps.setString(6, appMenuResource.getMenuIcon());
			ps.setString(7, appMenuResource.getMenuSortNo());
			flag = ps.executeUpdate();
		} catch (SQLException e) {
			throw new SQLException("queryAppFrameStartWithKeys is Wrong!"
					+ e.getMessage());
		} finally {
			close1(conn, ps);
		}
		return flag;

	}

	/**
	 * 修改模块参数
	 * 
	 * @param appModuleParam
	 * @return
	 * @throws SQLException
	 * @throws ClassNotFoundException
	 */
	public int update(AppMenuResource appMenuResource) throws SQLException,
			ClassNotFoundException {
		Connection conn = ConnectionManager.getConnection();
		PreparedStatement ps = null;
		int flag;
		try {
			String sql = "update  app_menu_resource  set MENU_NAME=?,PARENT_MENU_ID=?,MENU_URL=?,MENU_ICON=?,MENU_SORT_NO=? where app_id=? and MENU_ID=? ";
			ps = conn.prepareStatement(sql);
			ps.setString(1, appMenuResource.getMenuName());
			ps.setString(2, appMenuResource.getParentMenuId());
			ps.setString(3, appMenuResource.getMenuUrl());
			ps.setString(4, appMenuResource.getMenuIcon());
			ps.setString(5, appMenuResource.getMenuSortNo());
			ps.setString(6, appMenuResource.getAppId());
			ps.setString(7, appMenuResource.getMenuId());

			flag = ps.executeUpdate();
		} catch (SQLException e) {
			throw new SQLException("queryAppFrameStartWithKeys is Wrong!"
					+ e.getMessage());
		} finally {
			close1(conn, ps);
		}
		return flag;

	}

	public int delete(String appId, String menuId) throws SQLException {
		// TODO Auto-generated method stub
		Connection conn = ConnectionManager.getConnection();
		PreparedStatement ps = null;
		int flag;
		try {
			String sql = "delete from app_menu_resource where app_id=? and MENU_ID=? ";
			ps = conn.prepareStatement(sql);
			ps.setString(1, appId);
			ps.setString(2, menuId);

			flag = ps.executeUpdate();
		} catch (SQLException e) {
			throw new SQLException("queryAppFrameStartWithKeys is Wrong!"
					+ e.getMessage());
		} finally {
			close1(conn, ps);
		}
		return flag;
	}

	public int insertRoleRight(String menuStr, String appId, String appRoleId)
			throws SQLException {
		// TODO Auto-generated method stub
		Connection conn = ConnectionManager.getConnection();
		PreparedStatement ps = null;
		int flag = -1;
		String sql = "delete  from app_role_right  where app_id=?  and  app_role_id=?";
		String sql1 = "insert into app_role_right  values(?,?,?)";
		String menuids[] = menuStr.split(",");
		try {
			if (menuids.length > 0) {
				ps = conn.prepareStatement(sql);
				ps.setString(1, appId);
				ps.setString(2, appRoleId);
				flag = ps.executeUpdate();

				for (int i = 0; i < menuids.length; i++) {
					ps = conn.prepareStatement(sql1);
					ps.setString(1, appId);
					ps.setString(2, appRoleId);
					ps.setString(3, menuids[i]);
					flag = ps.executeUpdate();
				}

			}
		} catch (SQLException e) {
			throw new SQLException("queryAppFrameStartWithKeys is Wrong!"
					+ e.getMessage());
		} finally {
			close1(conn, ps);
		}
		return flag;
	}

	/**
	 * 关闭资源
	 * 
	 * @param theRs
	 * @param theStmt
	 * @param thePs
	 */
	protected void close(ResultSet theRs, Statement theStmt,
			PreparedStatement thePs) {
		try {
			if (theRs != null)
				theRs.close();
			if (theStmt != null)
				theStmt.close();
			if (thePs != null)
				thePs.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	/**
	 * 关闭资源
	 * 
	 * @param theRs
	 * @param theStmt
	 * @param thePs
	 */
	protected void close1(Connection conn, PreparedStatement thePs) {
		try {
			if (conn != null)
				conn.close();
			if (thePs != null)
				thePs.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	/**
	 * 关闭资源
	 * 
	 * @param theRs
	 * @param theStmt
	 * @param thePs
	 */
	protected void close2(ResultSet theRs, Connection conn,
			PreparedStatement thePs) {
		try {
			if (theRs != null)
				theRs.close();
			if (conn != null)
				conn.close();
			if (thePs != null)
				thePs.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

}
